9.12 高级查询操作

9.12.1 关联数据模型​

• Person
• PersonDetail
• Children
• Post

using Furion.DatabaseAccessor;  
using System;  
using System.Collections.Generic;  
using System.ComponentModel.DataAnnotations;  

namespace Furion.Core  
{  
    public class Person : Entity  
    {  
        /// <summary>  
        /// 构造函数  
        /// </summary>  
        public Person()  
        {  
            CreatedTime = DateTime.Now;  
        }  

        /// <summary>  
        /// 姓名  
        /// </summary>  
        [MaxLength(32)]  
        public string Name { get; set; }  

        /// <summary>  
        /// 年龄  
        /// </summary>  
        public int Age { get; set; }  

        /// <summary>  
        /// 住址  
        /// </summary>  
        public string Address { get; set; }  

        /// <summary>  
        /// 从表  
        /// </summary>  
        public PersonDetail Detail { get; set; }  

        /// <summary>  
        /// 一对多  
        /// </summary>  
        public ICollection<Children> Childrens { get; set; }  

        /// <summary>  
        /// 多对多  
        /// </summary>  
        public ICollection<Post> Posts { get; set; }  
    }  
}  

using Furion.DatabaseAccessor;  

namespace Furion.Core  
{  
    public class PersonDetail : EntityBase  
    {  
        /// <summary>  
        /// 电话号码  
        /// </summary>  
        public string PhoneNumber { get; set; }  

        /// <summary>  
        /// QQ 号码  
        /// </summary>  
        public string QQ { get; set; }  

        /// <summary>  
        /// 外键  
        /// </summary>  
        public int PersonId { get; set; }  

        /// <summary>  
        /// 主表  
        /// </summary>  
        public Person Person { get; set; }  
    }  
}  

using Furion.DatabaseAccessor;  
using System;  

namespace Furion.Core  
{  
    public class Children : Entity  
    {  
        /// <summary>  
        /// 构造函数  
        /// </summary>  
        public Children()  
        {  
            CreatedTime = DateTime.Now;  

        }  

        /// <summary>  
        /// 名称  
        /// </summary>  
        public string Name { get; set; }  

        /// <summary>  
        /// 性别  
        /// </summary>  
        public Gender Gender { get; set; }  

        /// <summary>  
        /// 外键  
        /// </summary>  
        public int PersonId { get; set; }  

        /// <summary>  
        /// 主表  
        /// </summary>  
        public Person Person { get; set; }  
    }  
}  

using Furion.DatabaseAccessor;  
using System;  
using System.Collections.Generic;  

namespace Furion.Core  
{  
    public class Post : Entity  
    {  
        /// <summary>  
        /// 构造函数  
        /// </summary>  
        public Post()  
        {  
            CreatedTime = DateTime.Now;  

        }  

        /// <summary>  
        /// 名称  
        /// </summary>  
        public string Name { get; set; }  

        /// <summary>  
        /// Person 集合  
        /// </summary>  
        public ICollection<Person> Persons { get; set; }  
    }  
}  

9.12.2 一对一查询​

// 示例一  
var person = repository.Include(u => u.Detail);  

// 示例二  
var person = repository.Include(u => u.Detail)  
                       .Include(u => u.Posts);  

// 示例三  
var person = repository.Include(u => u.Detail)  
                            .ThenInclude(d => d.Review)  
                       .Include(u => u.Posts);  

// 示例四  
var person = repository.Include(u => u.Detail.Where(d => d.Id > 10).OrderBy(d => d.Name))  
                            .ThenInclude(d => d.Review)  
                       .Include(u => u.Posts);  

// 示例五  
var person = repository.Include(!string.IsNullOrEmpty(keyword), u => u.Detail);  

// 示例六  
var person = repository.Include(!string.IsNullOrEmpty(keyword), u => u.Detail)  
                       .Include(age > 18, u => u.Detail.Where(d => d.Id > 10).OrderBy(d => d.Name))   
                            .ThenInclude(d => d.Review)  
                       .Include(u => u.Posts);  

9.12.3 一对多查询​

// 示例一  
var person = repository.Include(u => u.Childrens);  

// 参考 一对一 例子  

特别说明一对一 和 一对多 查询方法一样，唯一的区别是：一对多 采用 ICollection<TEntity> 定义属性。

9.12.4 多对多查询​

// 示例一  
var person = repository.Include(u => u.Posts);  

// 参考 一对一 例子  

特别说明一对一 和 多对多 查询方法一样，唯一的区别是：多对多 采用 ICollection<TEntity> 定义属性。

9.12.5 联表查询​

9.12.5.1 内连接 Inner Join​

var query = from p in _personRepository.AsQueryable()  
            join d in _personDetailRepository.AsQueryable() on p.Id equals d.PersonId  
            select new PersonDto  
            {  
                PhoneNumber = p.PersonDetail.PhoneNumber,  
                Address = p.Address,  
                Age = p.Age,  
                Name = p.Name,  
                Id = p.Id,  
                QQ = p.PersonDetail.QQ  
            };  

9.12.5.2 左连接 Left Join​

var query = from p in _personRepository.AsQueryable()  
            join d in _personDetailRepository.AsQueryable() on p.Id equals d.PersonId into results  
            from d in results.DefaultIfEmpty()  
            select new PersonDto  
            {  
                PhoneNumber = p.PersonDetail.PhoneNumber,  
                Address = p.Address,  
                Age = p.Age,  
                Name = p.Name,  
                Id = p.Id,  
                QQ = p.PersonDetail.QQ  
            };  

小提示Left Join 和 Inner Join 不同的是，Left Join 会先将结果 into 到新的结果集然后再查询，并调用 DefaultIfEmpty() 方法。

9.12.5.3 右连接 Right Join​

Right Join 只需要将 Left Join 主从表位置更换即可。

9.12.6 分组查询​

// 示例一  
var query = repository.AsQueryable().GroupBy(x => new { x.Column1, x.Column2 });  

// 示例二  
var query = from student in repository.AsQueryable()  
            group student by repository2.AsQueryable() into dateGroup  
            select new ResultData()  
            {  
                Key = dateGroup.Key,  
                Value = dateGroup.Count()  
            };  

// 示例三  
var query = from a in repository.AsQueryable()  
            join b in repository2.AsQueryable() on a.Id equals b.Aid  
            join c in repository3.AsQueryable() on c.id equals b.Bid  
            group a by new { a.Age, b.Sex } into g  
            select new {  
                Peo = g.Key,  
                Count = g.Count()  
            };  

9.12.7 合并结果集​

var query = repository.AsQueryable(u => u.Id > 10)  
                      .Union(  
                          repository2.AsQueryable(u => u.Id <= 10)  
                      );  

9.12.8 查询排序​

9.12.8.1 正序​

// 示例一  
var query = repository.AsQueryable()  
                      .OrderBy(u => u.Id);  

// 示例二  
var query =repository.AsQueryable()  
                     .OrderBy(u => u.Id)  
                     .ThenBy(u => u.Name);  

9.12.8.2 倒序​

// 示例一  
var query = repository.AsQueryable()  
                      .OrderByDescending(u => u.Id);  

// 示例二  
var query =repository.AsQueryable()  
                     .OrderByDescending(u => u.Id)  
                     .ThenByDescending(u => u.Name);  

9.12.8.3 混合倒序​

// 示例一  
var query = repository.AsQueryable()  
                      .OrderBy(u => u.Id)  
                      .OrderByDescending(u => u.Name)  
                      .ThenBy(u => u.Age);  

9.12.9 递归查询​

• City
• CityDto

using Furion.DatabaseAccessor;  
using Microsoft.EntityFrameworkCore;  
using Microsoft.EntityFrameworkCore.Metadata.Builders;  
using System;  
using System.Collections.Generic;  

namespace Furion.Core  
{  
    /// <summary>  
    /// 城市  
    /// </summary>  
    public class City : Entity, IEntityTypeBuilder<City>, IEntitySeedData<City>  
    {  
        /// <summary>  
        /// 构造函数  
        /// </summary>  
        public City()  
        {  
            CreatedTime = DateTime.Now;  

        }  

        /// <summary>  
        /// 名称  
        /// </summary>  
        public string Name { get; set; }  

        /// <summary>  
        /// 上级Id  
        /// </summary>  
        public int? ParentId { get; set; }  

        /// <summary>  
        /// 上级  
        /// </summary>  
        public virtual City Parent { get; set; }  

        /// <summary>  
        /// 子集  
        /// </summary>  
        public virtual ICollection<City> Childrens { get; set; }  

        /// <summary>  
        /// 配置实体关系  
        /// </summary>  
        /// <param name="entityBuilder"></param>  
        /// <param name="dbContext"></param>  
        /// <param name="dbContextLocator"></param>  
        public void Configure(EntityTypeBuilder<City> entityBuilder, DbContext dbContext, Type dbContextLocator)  
        {  
            entityBuilder  
                .HasMany(x => x.Childrens)  
                .WithOne(x => x.Parent)  
                .HasForeignKey(x => x.ParentId)  
                .OnDelete(DeleteBehavior.ClientSetNull); // 必须设置这一行  
        }  

        /// <summary>  
        /// 种子数据  
        /// </summary>  
        /// <param name="dbContext"></param>  
        /// <param name="dbContextLocator"></param>  
        /// <returns></returns>  
        public IEnumerable<City> HasData(DbContext dbContext, Type dbContextLocator)  
        {  
            return new List<City>  
            {  
                new City { Id=1,CreatedTime =DateTime.Parse("2020-08-20 15:30:20"),IsDeleted=false,Name="中国" },  
                new City { Id=2,CreatedTime =DateTime.Parse("2020-08-20 15:30:20"),IsDeleted=false,Name="广东省",ParentId=1 },  
                new City { Id=3,CreatedTime =DateTime.Parse("2020-08-20 15:30:20"),IsDeleted=false,Name="中山市",ParentId=2 },  
                new City { Id=4,CreatedTime =DateTime.Parse("2020-08-20 15:30:20"),IsDeleted=false,Name="珠海市",ParentId=2 },  
                new City { Id=5,CreatedTime =DateTime.Parse("2020-08-20 15:30:20"),IsDeleted=false,Name="浙江省",ParentId=1 },  
            };  
        }  
    }  
}  

using System.Collections.Generic;  

namespace Furion.Application.Persons  
{  
    public class CityDto  
    {  
        /// <summary>  
        /// 主键  
        /// </summary>  
        public int Id { get; set; }  

        /// <summary>  
        /// 名称  
        /// </summary>  
        public string Name { get; set; }  

        /// <summary>  
        /// 子集  
        /// </summary>  
        public ICollection<CityDto> Childrens { get; set; }  
    }  
}  

var cities = await repository.AsQueryable()  
                             .Include(u => u.Childrens)  
                             .Where(u => u.Id == 1)  
                             .ToListAsync();  

var dtos = cities.Adapt<List<CityDto>>();  

9.12.10 动态 Sql 查询​

Furion 默认不支持 动态 Sql 查询功能，不过可以通过第三方实现：

在 Furion 项目层安装 System.Linq.Dynamic.Core 包 https://github.com/zzzprojects/System.Linq.Dynamic.Core

9.12.10.1 动态 Sql​

// 示例一  
var query = repository.AsQueryable()  
                      .Where("City == @0 and Orders.Count >= @1", "China", 10)  
                      .OrderBy("CompanyName")  
                      .Select("new(CompanyName as Name, Phone)");  

// 示例二  
var list = repository.AsQueryable()  
                     .Where("Name.Contains(@0)","Furion")  
                     .ToList();  

// 示例三，支持 ? 语法  
var customers = repository.AsQueryable()  
                          .Include(c => c.Location)  
                          .Where(c => c.Location?.Name == "test") // 注意 Location?.Name  
                          .ToList();  

9.12.10.2 动态 Lambda​

// 示例一  
var x = Expression.Parameter(typeof(int), "x");  
var y = Expression.Parameter(typeof(int), "y");  
var e = DynamicExpressionParser  
            .ParseLambda(new ParameterExpression[] { x, y }, null, "(x + y) * 2");  

// 示例二  
var e = DynamicExpressionParser.ParseLambda(  
        typeof(Customer), typeof(bool),  
        "City = @0 and Orders.Count >= @1",  
        "London", 10);  

9.12.11 时态查询​

功能移除声明以下内容在 Furion 2.13 + 版本中已移除。

Furion 框架还提供了时态查询功能，可以查询特定时间的数据，如：

var result = rep.Entities  
    .AsTemporalOf(DateTime.UtcNow.AddDays(-1))  
    .Include(i=> i.Company)  
    .FirstOrDefault(i => i.Name == "Furion");  

另外提供了多个时态查询方法

• AsTemporalAll()
• AsTemporalAsOf(date)
• AsTemporalFrom(startDate, endDate)
• AsTemporalBetween(startDate, endDate)
• AsTemporalContained(startDate, endDate)

9.12.12 性能优化​

默认情况下，EF Core 会跟踪所有实体，也就是任何数据改变都会引起数据检查，所以如果只做查询操作，建议关闭实体跟踪功能。

Furion 框架提供了以下高性能实体集合：

• DetachedEntities：脱轨/不追踪实体
• AsQueryable(false)：不追踪实体
• Entities.AsNoTracking()：手动关闭实体追踪

在 EF Core 中，复杂查询总是会生成一个 sql，也就是 AsSingleQuery()，我们也可以设置为 AsSplitQuery() 切割成多个查询。

9.12.13 分表查询小例子​

using Furion.DatabaseAccessor;  
using Microsoft.EntityFrameworkCore;  
using Microsoft.EntityFrameworkCore.Metadata.Builders;  
using System;  
using System.Collections.Generic;  

namespace Furion.Core  
{  
    public class Person : Entity, IEntityTypeBuilder<Person>  
    {  
        public string Name { get; set; }  

        /// <summary>  
        /// 配置实体关系  
        /// </summary>  
        /// <param name="entityBuilder"></param>  
        /// <param name="dbContext"></param>  
        /// <param name="dbContextLocator"></param>  
        public void Configure(EntityTypeBuilder<Person> entityBuilder, DbContext dbContext, Type dbContextLocator)  
        {  
            entityBuilder.ToSqlQuery(  
              @"select * from dbo.person.2020-09-19  
                union all  
                select * from dbo.person.2020-09-20");  
        }  
    }  
}  

var posts = repository.Where(u => u.Id > 10).ToList();  

9.12.14 反馈与建议​

与我们交流给 Furion 提 Issue。